﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Model;
using Tools;
using System.Data;
using System.Data.SqlClient;
namespace DAL
{
    public class ManageBooksDAL
    {
        #region 图书类型表
        /// <summary>
        /// 查询图书类型
        /// </summary>
        /// <returns></returns>
        public static List<BookType> BookTypeList()
        {
            string str = "select*from BookType";
            SqlDataReader sdr = Tools.DBHelper.ExecuteQuery(str);
            List<BookType> BookTypeList = new List<BookType>();
            while (sdr.Read())
            {
                BookType BookType1 = new BookType();
                BookType1.TypeID = (int)sdr["TypeID"];
                BookType1.TypeName = (string)sdr["TypeName"];
                BookType1.State = (int)sdr["State"];
                BookTypeList.Add(BookType1);
            }
            sdr.Close();
            return BookTypeList;
        }
        /// <summary>
        /// 删除图书类型
        /// </summary>
        /// <param name="id">参数：id</param>
        public static void RemoveBookType(int id)
        {
            string str = "delete from BookType where TypeID=" + id;
            DBHelper.Execute(str);
        }

        /// <summary>
        /// 添加图书类型
        /// </summary>
        /// <param name="item">图书类型对象</param>
        public static void AddBookType(BookType item)
        {
            string str = string.Format("insert into BookType values('{0}','{1}')", item.TypeName, item.State);
            DBHelper.Execute(str);
        }

        /// <summary>
        /// 修改图书类型
        /// </summary>
        /// <param name="item">图书类型对象</param>
        public static void EditBookType(BookType item)
        {
            string str = string.Format("update BookType set TypeName='{0}',State={1} where TypeID={3}", item.TypeName, item.State, item.TypeID);
            DBHelper.Execute(str);
        }
        #endregion



        #region 图书信息表
        /// <summary>
        /// 查询图书信息
        /// </summary>
        /// <returns></returns>
        public static List<BookInfo> BookInfoList()
        {
            string str = "select*from BookInfo where State=0";
            SqlDataReader sdr = Tools.DBHelper.ExecuteQuery(str);
            List<BookInfo> BookInfoList = new List<BookInfo>();
            List<BookType> TypeList = BookTypeList();
            
            while (sdr.Read())
            {
                BookInfo BookInfo = new BookInfo();
                BookInfo.BookID = (int)sdr["BookID"];
                BookInfo.BookNum = (string)sdr["BookNum"];
                BookInfo.BookName = (string)sdr["BookName"];
                BookInfo.BookDesc = (string)sdr["BookDesc"];
                BookInfo.Author = (string)sdr["Author"];
                BookInfo.OriginalPrice = (decimal)sdr["OriginalPrice"];
                BookInfo.PresentPrice = (decimal)sdr["PresentPrice"];
                BookInfo.Pubishing = (string)sdr["Pubishing"];
                BookInfo.PublicaDate = (DateTime)sdr["PublicaDate"];
                BookInfo.Pic = (string)sdr["Pic"];
                BookInfo.TypeID = (int)sdr["TypeID"];
                BookInfo.State = (int)sdr["State"];
                BookInfo.Chapter = (string)sdr["Chapter"];
                BookInfo.BookDescMax = (string)sdr["BookDescMax"];
                BookInfo.AuthorDesc = (string)sdr["AuthorDesc"];
                BookInfo.Preface = (string)sdr["Preface"];
                BookInfo.BookType = TypeList.Where(a => a.TypeID == BookInfo.TypeID).SingleOrDefault();
                BookInfoList.Add(BookInfo);
            }
            sdr.Close();
            return BookInfoList;
        }

        /// <summary>
        /// 删除图书
        /// </summary>
        /// <param name="id">参数：id</param>
        public static void RemoveBookInfo(int id)
        {
            string str = "update BookInfo set State=1 where BookID=" + id;
            DBHelper.Execute(str);
        }
        /// <summary>
        /// 添加图书
        /// </summary>
        /// <param name="item"></param>
        public static void AddBookInfo(BookInfo item)
        {
            string str = string.Format("insert into BookInfo values('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}','{14}')", item.BookNum, item.BookName, item.PresentPrice, item.PresentPrice, item.BookDesc, item.Pic, item.TypeID, item.Author, item.Pubishing, item.PublicaDate, item.Chapter, item.State, item.BookDescMax, item.AuthorDesc, item.Preface);
            DBHelper.Execute(str);
        }
        /// <summary>
        /// 修改图书信息
        /// </summary>
        /// <param name="item">图书对象</param>
        public static void EditBookInfo(BookInfo item)
        {
            string str = string.Format("update BookInfo set BookNum='{0}',BookName='{1}',OriginalPrice='{2}',PresentPrice='{3}',BookDesc='{4}',TypeID={5},Author='{6}',Pubishing='{7}',Pic='{8}',PublicaDate='{9}',BookDescMax='{10}',AuthorDesc='{11}',Preface='{12}' where BookID={13}", item.BookNum, item.BookName, item.OriginalPrice, item.PresentPrice, item.BookDesc, item.TypeID, item.Author, item.Pubishing, item.Pic, item.PublicaDate, item.BookDescMax, item.AuthorDesc, item.Preface, item.BookID);
            DBHelper.Execute(str);
        }
        /// <summary>
        /// 添加图片
        /// </summary>
        /// <param name="PicUrl">图片路径</param>
        /// <param name="id">图书id</param>
        public static void AddBookPic(string PicUrl, int id = 0)
        {
            string str = string.Format("update BookInfo set Pic='{0}' where BookID='{1}'", PicUrl, id);
            DBHelper.Execute(str);
        }
        #endregion

        #region 图书评论表
        /// <summary>
        /// 查询图书评论
        /// </summary>
        /// <returns></returns>
        public static List<Comments> BookComList()
        {
            string str = "select*from Comment";
            SqlDataReader sdr = Tools.DBHelper.ExecuteQuery(str);
            List<Comments> BookComList = new List<Comments>();
            while (sdr.Read())
            {
                Comments BookCom = new Comments();
                BookCom.BookID = (int)sdr["BookID"];
                BookCom.ComID = (int)sdr["ComID"];
                BookCom.UserID = (int)sdr["UserID"];
                BookCom.ComDate = (DateTime)sdr["ComDate"];
                BookCom.ComDesc = (string)sdr["ComDesc"];
                BookCom.State = (int)sdr["State"];
                BookCom.UserInfo = ManageUserDAL.userdata().Where(a=>a.UserID==BookCom.UserID).SingleOrDefault();
                BookCom.BookInfo = BookInfoList().Where(a => a.BookID == BookCom.BookID).SingleOrDefault();
                BookComList.Add(BookCom);
            }
            sdr.Close();
            return BookComList;
        }
        #endregion
    }
}
